package com.zzy.demo.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;
import com.google.common.collect.Lists;
import com.zzy.demo.common.dto.export.ColHeaderVO;
import com.zzy.demo.common.util.export.TopEasyExcelUtil;

@Service
public class ExportService {
	
	
    @Autowired
    JdbcTemplate jdbcTemplate;
	
	
	
	public void exportByCursor() {
		//导出分组
        String sql = "";
        
        String newFilePath ="";
        
        
        List<ColHeaderVO> colList = Lists.newArrayList();
        
        
        //构建ExcelWriterBuilder
        List<List<String>> buildHead = TopEasyExcelUtil.buildHead(colList);
        // 这里 需要指定写用哪个class去写
        com.alibaba.excel.ExcelWriter excelWriter = EasyExcel.write(newFilePath).build();
        // 这里注意 如果同一个sheet只要创建一次
        
		Object numObj = jdbcTemplate.execute(new ConnectionCallback<Object>() {
			@Override
			public Object doInConnection(Connection con) throws SQLException, DataAccessException {
				PreparedStatement ps = null;
				ResultSet rs = null;
				con.setAutoCommit(false);
				jdbcTemplate.setFetchSize(1000);
				
				
				
				String sheetName = "sheet1";
		        //WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(buildHead).build();
				VerticalCellStyleStrategy styleStrategy = new VerticalCellStyleStrategy(colList);
				WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
						.registerWriteHandler(styleStrategy).head(buildHead).build();

				Long num = 0L;
				
				
				//缓存数据
		        List<List<Object>> dataList = Lists.newLinkedList();
				try {
					ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					ps.execute();
					rs = ps.getResultSet();
					
					int i = 1;
			        int sheetNum =1;
					while (rs.next()) {
						// 写入到excel
						List<Object> rowData = Lists.newLinkedList();
						String divisionCode = "";
						for(ColHeaderVO colHeaderVO : colList) {
							String columnId = colHeaderVO.getColumnId();
							String columnVal = rs.getString(columnId);
							rowData.add(columnVal);
						}
						dataList.add(rowData);
						
						//每1000条数据分批处理
						if (dataList.size() == 1000) {
                            // insert
							excelWriter.write(dataList, writeSheet);
							dataList = Lists.newLinkedList();
                        }
						
						
						i++;
						num++;
						
						//分sheet
			            if(i>500000) {
			                sheetName = "sheet"+  ++sheetNum;
			                //writeSheet = EasyExcel.writerSheet(sheetName).head(buildHead).build();
							styleStrategy = new VerticalCellStyleStrategy(colList);
							writeSheet = EasyExcel.writerSheet(sheetName).registerWriteHandler(styleStrategy).head(buildHead).build();
			                i=0;
			            }
			            
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (rs != null) {
						rs.close();
					}
					if (ps != null) {
						ps.close();
					}
				}
				
				
				
				//写入剩余数据
				excelWriter.write(dataList, writeSheet);
		        excelWriter.finish();
				
				con.setAutoCommit(true);
				return num;
			}
		});
		
		
		
		
		
		

	}
	
	
	
	
	
	
	class VerticalCellStyleStrategy extends AbstractVerticalCellStyleStrategy {

		private List<ColHeaderVO> columns;

		public VerticalCellStyleStrategy(List<ColHeaderVO> columns) {
			this.columns = columns;
		}

		protected WriteCellStyle headCellStyle(CellWriteHandlerContext context) {
			int colIndex = context.getHeadData().getColumnIndex();
			WriteCellStyle cs = new WriteCellStyle();
			WriteFont headWriteFont = new WriteFont();
			headWriteFont.setFontHeightInPoints((short) 11);
			cs.setWriteFont(headWriteFont);
			int width = columns.get(colIndex).getMinWidth();
			width = width / 7 - 2;
			if (width > 255) {
				width = 255;
			}
			context.getWriteSheetHolder().getSheet().setColumnWidth(colIndex, width * 256);
			return cs;
		}

		protected WriteCellStyle contentCellStyle(CellWriteHandlerContext context) {
			ColHeaderVO ic = columns.get(context.getHeadData().getColumnIndex());
			WriteCellStyle cs = new WriteCellStyle();
			if ("center".equals(ic.getAlign())) {
				cs.setHorizontalAlignment(HorizontalAlignment.CENTER);
			} else if ("right".equals(ic.getAlign())) {
				cs.setHorizontalAlignment(HorizontalAlignment.RIGHT);
			}
			return cs;
		}

	}

}
